/**
 * 
 */
package com.feib.stms.dao.jdbc;

import java.math.BigDecimal;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang.StringUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.jdbc.support.incrementer.OracleSequenceMaxValueIncrementer;

import com.feib.stms.dao.TempPaymentDao;
import com.feib.stms.model.BaseTempPayment;
import com.feib.stms.model.TempUploadFile;
import com.feib.stms.pagination.PaginatedList;

/**
 * @author Jimmy Liu
 *
 */
public abstract class BaseTempPaymentJdbcDao<T extends BaseTempPayment> extends
BaseJdbcDao implements TempPaymentDao<T> {

	protected OracleSequenceMaxValueIncrementer sequence;

	protected SimpleJdbcInsert tempTempPaymentJdbcInsert;

	/**
	 * 給 PK 自動產生號碼的 sequence 名稱
	 * @return
	 */
	protected abstract String getSequenceName();

	/**
	 * Table 名稱
	 * @return
	 */
	protected abstract String getTableName();

	/**
	 * Table Pk 欄位名稱
	 * @return
	 */
	protected abstract String getTablePkName();

	protected abstract void setTablePKValue(T baseTempPayment, long pkValue);


	@Override
	protected void initDao() throws Exception {
		super.initDao();
		this.sequence = new  OracleSequenceMaxValueIncrementer(this.getDataSource(), getSequenceName());
		this.tempTempPaymentJdbcInsert = new  SimpleJdbcInsert(this.getDataSource());
		this.tempTempPaymentJdbcInsert.withTableName(getTableName())
		.usingColumns(getTablePkName(), "TEMP_UPLOAD_FILE_PK", "GROUP_PK", "ACCOUNT_NO", "DELIVERY_DATE"
				, "NET_PAY", "NET_INCOME", "ACCOUNT_NO_CHK", "AMOUNT_CHK", "DEAL_TYPE"
				, "CUSTOMER_NAME", "STOCK_NO", "STOCK_NAME", "CHARGEBACK_PRIORITY", "CHARGEBACK_PRIORITY_CODE"
				, "TELLER", "DEAL_RESULT_CODE", "CUSTOMER_ID", "SECURITIES_NO"
				, "NET_PAY_CHKSUM", "NET_INCOME_CHKSUM", "ACCOUNT_NO_CHKSUM", "CUSTOMER_ID_CHKSUM", "CHKSUM"
				, "UPDATED_DATE", "UPDATED_BY", "CREATED_DATE", "CREATED_BY","PRE_DESIGNATED_ACCOUNT");
	}

	/* (non-Javadoc)
	 * @see com.feib.stms.dao.TempPaymentDao#insert(com.feib.stms.model.BaseTempPayment)
	 */
	public int insert(T entity) {
		List<T> entities = new ArrayList<T>();
		entities.add(entity);

		return this.insert(entities);
	}

	/* (non-Javadoc)
	 * @see com.feib.stms.dao.TempPaymentDao#insert(java.util.List)
	 */
	public int insert(List<T> entities) {
		int result = 0;

		if (CollectionUtils.isNotEmpty(entities))
		{
			List<SqlParameterSource> params = new ArrayList<SqlParameterSource>(entities.size());

			for (T baseTempPayment: entities)
			{
				MapSqlParameterSource parameters = new MapSqlParameterSource();

				Long tempUploadFileErrorPk = new Long(sequence.nextLongValue());
				parameters.addValue(getTablePkName(), tempUploadFileErrorPk, Types.NUMERIC);
				parameters.addValue("TEMP_UPLOAD_FILE_PK", baseTempPayment.getTempUploadFile().getTempUploadFilePk(), Types.NUMERIC);
				parameters.addValue("GROUP_PK", baseTempPayment.getGroup().getGroupPk(), Types.NUMERIC);
				parameters.addValue("ACCOUNT_NO", baseTempPayment.getAccountNo(), Types.VARCHAR);
				parameters.addValue("DELIVERY_DATE", baseTempPayment.getDeliveryDate(), Types.TIMESTAMP);

				parameters.addValue("NET_PAY", baseTempPayment.getNetPay(), Types.DECIMAL);
				parameters.addValue("NET_INCOME", baseTempPayment.getNetIncome(), Types.DECIMAL);
				parameters.addValue("ACCOUNT_NO_CHK", baseTempPayment.getAccountNoChk(), Types.VARCHAR);
				parameters.addValue("AMOUNT_CHK", baseTempPayment.getAmountChk(), Types.VARCHAR);
				parameters.addValue("DEAL_TYPE", baseTempPayment.getDealType(), Types.VARCHAR);

				parameters.addValue("CUSTOMER_NAME", baseTempPayment.getCustomerName(), Types.VARCHAR);
				parameters.addValue("STOCK_NO", baseTempPayment.getStockNo(), Types.VARCHAR);
				parameters.addValue("STOCK_NAME", baseTempPayment.getStockName(), Types.VARCHAR);
				parameters.addValue("CHARGEBACK_PRIORITY", baseTempPayment.getChargebackPriority(), Types.VARCHAR);
				parameters.addValue("CHARGEBACK_PRIORITY_CODE", baseTempPayment.getChargebackPriorityCode(), Types.VARCHAR);

				parameters.addValue("TELLER", baseTempPayment.getTeller(), Types.VARCHAR);
				parameters.addValue("DEAL_RESULT_CODE",  baseTempPayment.getDealResultCode(), Types.VARCHAR);
				parameters.addValue("CUSTOMER_ID", baseTempPayment.getCustomerId(), Types.VARCHAR);
				parameters.addValue("SECURITIES_NO", baseTempPayment.getSecuritiesNo(), Types.VARCHAR);


				parameters.addValue("NET_PAY_CHKSUM", baseTempPayment.getNetPayChksum(), Types.NUMERIC);
				parameters.addValue("NET_INCOME_CHKSUM", baseTempPayment.getNetIncomeChksum(), Types.NUMERIC);
				parameters.addValue("ACCOUNT_NO_CHKSUM", baseTempPayment.getAccountNoChksum(), Types.NUMERIC);
				parameters.addValue("CUSTOMER_ID_CHKSUM", baseTempPayment.getCustomerIdChksum(), Types.NUMERIC);
				parameters.addValue("CHKSUM", baseTempPayment.getChksum(), Types.NUMERIC);

				parameters.addValue("UPDATED_DATE", baseTempPayment.getUpdatedDate(), Types.TIMESTAMP);
				parameters.addValue("UPDATED_BY", baseTempPayment.getUpdatedBy(), Types.VARCHAR);
				parameters.addValue("CREATED_DATE", baseTempPayment.getCreatedDate(), Types.TIMESTAMP);
				parameters.addValue("CREATED_BY", baseTempPayment.getCreatedBy(), Types.VARCHAR);
				
				// 約定帳號 setPreDesignatedAccount
				parameters.addValue("PRE_DESIGNATED_ACCOUNT", baseTempPayment.getPreDesignatedAccount(), Types.VARCHAR);

				params.add(parameters);
				setTablePKValue(baseTempPayment, tempUploadFileErrorPk);
			}			

			int[] batchCount = tempTempPaymentJdbcInsert.executeBatch(params.toArray(new MapSqlParameterSource[0]));
			for(int i: batchCount)
				result = result + i;

		}

		return result;
	}

	public int deleteByTempUploadFilePk(Long pk) {
		StringBuffer sql = new StringBuffer();
		MapSqlParameterSource parameters = new MapSqlParameterSource();
		sql.append("DELETE FROM ").append(this.getTableName()).append(" WHERE TEMP_UPLOAD_FILE_PK = :TEMP_UPLOAD_FILE_PK");
		parameters.addValue("TEMP_UPLOAD_FILE_PK", pk, Types.NUMERIC);

		return this.getSimpleJdbcTemplate().update(sql.toString(), parameters);
	}

	@SuppressWarnings("unchecked")
	public PaginatedList<T> queryForPaginatedListByTempUploadFilePk(Long pk, PaginatedList<T> paginatedList)
	{
		Class<T> tempPaymentClazz = (Class<T>) ((java.lang.reflect.ParameterizedType) getClass().getGenericSuperclass()).getActualTypeArguments()[0]; 

		SqlQueryMapProvider sqlQueryMapProvider = getSqlQueryMapProviderByTempUploadFilePk(pk, paginatedList);

		PaginatedList<T> result =  this.queryForPaginatedList(sqlQueryMapProvider, new BeanPropertyRowMapper<T>(tempPaymentClazz), paginatedList.getPageNumber(), paginatedList.getPageSize());

		/* 最後一頁，計算總計 */
		if (null != result && result.isLastPage())
		{
			sqlQueryMapProvider = getTotalSqlQueryMapProviderByTempUploadFilePk(pk);

			T totalObject = this.getSimpleJdbcTemplate().queryForObject(sqlQueryMapProvider.getSql(), new BeanPropertyRowMapper<T>(tempPaymentClazz), sqlQueryMapProvider.getParameters());

			result.setTotalObject(totalObject);

			/* 計算彙總*/
			sqlQueryMapProvider = getTotalSumSqlQueryMapProviderByTempUploadFilePk(pk);

			T totalObject2 = this.getSimpleJdbcTemplate().queryForObject(sqlQueryMapProvider.getSql(), new BeanPropertyRowMapper<T>(tempPaymentClazz), sqlQueryMapProvider.getParameters());

			result.setTotalObject2(totalObject2);


		}

		return result;
	}

	public SqlQueryMapProvider getTotalSqlQueryMapProviderByTempUploadFilePk(
			Long pk) {
		StringBuffer sql = new StringBuffer();
		Map<String, Object> parameters = new HashMap<String, Object>();

		sql.append("SELECT COUNT(*) AS RECORD_COUNT, SUM(sumRpt.NET_PAY) AS NET_PAY, SUM(sumRpt.NET_INCOME) AS NET_INCOME ");
		sql.append(", SUM(sumRpt.NET_INCOME - sumRpt.NET_PAY) AS NET_INCOME_PAY_DIFF");
		sql.append(", SUM(CASE WHEN sumRpt.Net_Pay > 0 THEN 1 ELSE 0 END) AS Net_Pay_COUNT");
		sql.append(", SUM(CASE WHEN sumRpt.NET_INCOME > 0 THEN 1 ELSE 0 END) AS NET_INCOME_COUNT");
		sql.append(", SUM(sumRpt.CHKSUM) AS CHKSUM, SUM(sumRpt.NET_PAY_CHKSUM) AS NET_PAY_CHKSUM , SUM(sumRpt.NET_INCOME_CHKSUM) AS NET_INCOME_CHKSUM ");
		sql.append(", SUM(sumRpt.ACCOUNT_NO_CHKSUM) AS ACCOUNT_NO_CHKSUM , SUM(sumRpt.CUSTOMER_ID_CHKSUM) AS CUSTOMER_ID_CHKSUM");

		SqlQueryMapProvider tmp = getSqlQueryMapProviderByTempUploadFilePk(pk, null);
		sql.append(" from ( ");
		sql.append(tmp.getSql());
		sql.append(" ) sumRpt ");

		parameters.putAll(tmp.getParameters());

		SqlQueryMapProvider sqlQueryMapProvider = new SqlQueryMapProvider(sql.toString(), parameters);

		logger.debug("{}", sqlQueryMapProvider);

		return sqlQueryMapProvider;
	}

	public SqlQueryMapProvider getSqlQueryMapProviderByTempUploadFilePk(
			Long pk, PaginatedList<T> paginatedList) 
	{
		StringBuffer sql = new StringBuffer();
		Map<String, Object> parameters = new HashMap<String, Object>();
		sql.append("SELECT D.* FROM ").append(this.getTableName()).append(" D WHERE TEMP_UPLOAD_FILE_PK = :TEMP_UPLOAD_FILE_PK");
		parameters.put("TEMP_UPLOAD_FILE_PK", pk);		

		if (null != paginatedList)
		{
			String orderBy = paginatedList.getSortCriterion();
			if (StringUtils.isNotEmpty(orderBy))
			{
				if (!orderBy.toUpperCase().startsWith("D."))
				{
					orderBy = "D." + orderBy;
				}

				sql.append(" ORDER BY ").append(orderBy).append(" ").append( 2==paginatedList.getSortDirection() ? "ASC" : "DESC" );
			}			
		}

		SqlQueryMapProvider sqlQueryMapProvider = new SqlQueryMapProvider(sql.toString(), parameters);

		return sqlQueryMapProvider;

	}

	public TempUploadFile getSummary(TempUploadFile tempUploadFile)
	{
		StringBuffer sql = new StringBuffer();
		Map<String, Object> parameters = new HashMap<String, Object>();
		sql.append("SELECT");
		sql.append(" COUNT(*) AS TOTAL_ITEMS");
		sql.append(", SUM(CASE WHEN NET_INCOME > 0  THEN 1 ELSE 0 END ) AS TOTAL_NET_INCOME_COUNT");
		sql.append(", SUM(CASE WHEN NET_PAY > 0  THEN 1 ELSE 0 END ) TOTAL_NET_PAY_COUNT");
		sql.append(", SUM(NET_PAY + NET_INCOME ) TOTAL_AMOUNT");
		sql.append(", SUM(NET_PAY) AS TOTAL_NET_PAY");
		sql.append(", SUM(NET_INCOME) AS TOTAL_NET_INCOME");
		sql.append(", SUM(CHKSUM) AS CHKSUM");
		sql.append(" FROM ").append(this.getTableName()).append(" WHERE TEMP_UPLOAD_FILE_PK = :TEMP_UPLOAD_FILE_PK");
		parameters.put("TEMP_UPLOAD_FILE_PK", tempUploadFile.getTempUploadFilePk());

		logger.debug("SQL: {}", sql.toString());
		logger.debug("Args: {}", parameters);

		Map<String, Object> record = this.getSimpleJdbcTemplate().queryForMap(sql.toString(), parameters);

		Number totalItems = (Number) record.get("TOTAL_ITEMS");
		Number totalAmount = (Number) record.get("TOTAL_AMOUNT");
		Number totalNetIncomeCount = (Number) record.get("TOTAL_NET_INCOME_COUNT");
		Number totalNetPayCount = (Number) record.get("TOTAL_NET_PAY_COUNT");
		Number totalNetPay = (Number) record.get("TOTAL_NET_PAY");
		Number totalNetIncome = (Number) record.get("TOTAL_NET_INCOME");
		Number chksum = (Number) record.get("CHKSUM");

		tempUploadFile.setTotalItems(totalItems.longValue());
		tempUploadFile.setTotalAmount(new BigDecimal(totalAmount.doubleValue()));
		tempUploadFile.setTotalNetIncomeCount(totalNetIncomeCount.longValue());
		tempUploadFile.setTotalNetPayCount(totalNetPayCount.longValue());

		tempUploadFile.setTotalNetPay(new BigDecimal(totalNetPay.doubleValue()));
		tempUploadFile.setTotalNetIncome(new BigDecimal(totalNetIncome.doubleValue()));
		tempUploadFile.setChksum(chksum.longValue());

		return tempUploadFile;
	}


	public SqlQueryMapProvider getTotalSumSqlQueryMapProviderByTempUploadFilePk(
			Long pk) {
		StringBuffer sql = new StringBuffer();
		Map<String, Object> parameters = new HashMap<String, Object>();

		sql.append("  SELECT sum(RECORD_COUNT) as RECORD_COUNT ");
		sql.append(", SUM(CASE WHEN (sumRpt.Net_Pay - sumRpt.NET_INCOME) > 0 ");
		sql.append("	THEN(sumRpt.Net_Pay - sumRpt.NET_INCOME) ELSE 0 END) AS NET_PAY");

		sql.append(", SUM(CASE WHEN (sumRpt.NET_INCOME - sumRpt.Net_Pay) > 0 ");
		sql.append("	THEN(sumRpt.NET_INCOME - sumRpt.Net_Pay) ELSE 0 END) AS NET_INCOME");

		sql.append(", SUM(sumRpt.NET_INCOME - sumRpt.NET_PAY) AS NET_INCOME_PAY_DIFF  ");

		sql.append(", SUM(CASE WHEN (sumRpt.Net_Pay - sumRpt.NET_INCOME) > 0 ");
		sql.append("	THEN 1 ELSE 0 END) AS Net_Pay_COUNT ");

		sql.append(", SUM(CASE WHEN (sumRpt.NET_INCOME - sumRpt.Net_Pay) > 0 ");
		sql.append("	THEN 1 ELSE 0 END) AS NET_INCOME_COUNT ");

		sql.append(" from ( ");
		sql.append(" SELECT COUNT(*) AS RECORD_COUNT,sum(D.Net_Pay) net_pay ");
		sql.append(" ,sum(d.net_income) net_income ");
		
		sql.append(" FROM ").append(this.getTableName()).append(" D");
		
		sql.append(" WHERE TEMP_UPLOAD_FILE_PK = :TEMP_UPLOAD_FILE_PK ");
		sql.append(" group by d.account_no");

		sql.append(" ) sumRpt ");

		parameters.put("TEMP_UPLOAD_FILE_PK", pk);

		SqlQueryMapProvider sqlQueryMapProvider = new SqlQueryMapProvider(sql.toString(), parameters);

		logger.debug("{}", sqlQueryMapProvider);

		return sqlQueryMapProvider;
	}

	
	public PaginatedList<T> queryChkForPaginatedListByUploadFilePk(Long pk, PaginatedList<T> paginatedList)
	{
		Class<T> paymentClazz = (Class<T>) ((java.lang.reflect.ParameterizedType) getClass().getGenericSuperclass()).getActualTypeArguments()[0]; 

		SqlQueryMapProvider sqlQueryMapProvider = getChkSqlQueryMapProviderByUploadFilePk(pk, paginatedList);

		PaginatedList<T> result =  this.queryForPaginatedList(sqlQueryMapProvider, new BeanPropertyRowMapper<T>(paymentClazz), paginatedList.getPageNumber(), paginatedList.getPageSize());

		/* 最後一頁，計算總計 */
		if (null != result && result.isLastPage())
		{
			sqlQueryMapProvider = getChkTotalSqlQueryMapProviderByUploadFilePk(pk);

			T totalObject = this.getSimpleJdbcTemplate().queryForObject(sqlQueryMapProvider.getSql(), new BeanPropertyRowMapper<T>(paymentClazz), sqlQueryMapProvider.getParameters());

			result.setTotalObject(totalObject);

			/* 計算彙總*/
			sqlQueryMapProvider = getChkTotalSumSqlQueryMapProviderByUploadFilePk(pk);

			T totalObject2 = this.getSimpleJdbcTemplate().queryForObject(sqlQueryMapProvider.getSql(), new BeanPropertyRowMapper<T>(paymentClazz), sqlQueryMapProvider.getParameters());

			result.setTotalObject2(totalObject2);


		}

		return result;
	}

	public SqlQueryMapProvider getChkSqlQueryMapProviderByUploadFilePk(
			Long pk, PaginatedList<T> paginatedList) 
	{
		StringBuffer sql = new StringBuffer();
		Map<String, Object> parameters = new HashMap<String, Object>();
		sql.append("SELECT D.* FROM DELIVERY_PAYMENT_CHK D WHERE UPLOAD_FILE_PK = :UPLOAD_FILE_PK");
		parameters.put("UPLOAD_FILE_PK", pk);		

		if (null != paginatedList)
		{
			String orderBy = paginatedList.getSortCriterion();
			if (StringUtils.isNotEmpty(orderBy))
			{
				if (!orderBy.toUpperCase().startsWith("D."))
				{
					orderBy = "D." + orderBy;
				}

				sql.append(" ORDER BY ").append(orderBy).append(" ").append( 2==paginatedList.getSortDirection() ? "ASC" : "DESC" );
			}			
		}

		SqlQueryMapProvider sqlQueryMapProvider = new SqlQueryMapProvider(sql.toString(), parameters);

		return sqlQueryMapProvider;

	}

	public SqlQueryMapProvider getChkTotalSqlQueryMapProviderByUploadFilePk(
			Long pk) {
		StringBuffer sql = new StringBuffer();
		Map<String, Object> parameters = new HashMap<String, Object>();

		sql.append("SELECT COUNT(*) AS RECORD_COUNT, SUM(sumRpt.NET_PAY) AS NET_PAY, SUM(sumRpt.NET_INCOME) AS NET_INCOME ");
		sql.append(", SUM(sumRpt.NET_INCOME - sumRpt.NET_PAY) AS NET_INCOME_PAY_DIFF");
		sql.append(", SUM(CASE WHEN sumRpt.Net_Pay > 0 THEN 1 ELSE 0 END) AS Net_Pay_COUNT");
		sql.append(", SUM(CASE WHEN sumRpt.NET_INCOME > 0 THEN 1 ELSE 0 END) AS NET_INCOME_COUNT");
		sql.append(", SUM(sumRpt.CHKSUM) AS CHKSUM, SUM(sumRpt.NET_PAY_CHKSUM) AS NET_PAY_CHKSUM , SUM(sumRpt.NET_INCOME_CHKSUM) AS NET_INCOME_CHKSUM ");
		sql.append(", SUM(sumRpt.ACCOUNT_NO_CHKSUM) AS ACCOUNT_NO_CHKSUM , SUM(sumRpt.CUSTOMER_ID_CHKSUM) AS CUSTOMER_ID_CHKSUM");

		SqlQueryMapProvider tmp = getChkSqlQueryMapProviderByUploadFilePk(pk, null);
		sql.append(" from ( ");
		sql.append(tmp.getSql());
		sql.append(" ) sumRpt ");

		parameters.putAll(tmp.getParameters());

		SqlQueryMapProvider sqlQueryMapProvider = new SqlQueryMapProvider(sql.toString(), parameters);

		logger.debug("{}", sqlQueryMapProvider);

		return sqlQueryMapProvider;
	}
	
	public SqlQueryMapProvider getChkTotalSumSqlQueryMapProviderByUploadFilePk(
			Long pk) {
		StringBuffer sql = new StringBuffer();
		Map<String, Object> parameters = new HashMap<String, Object>();

		sql.append("  SELECT sum(RECORD_COUNT) as RECORD_COUNT ");
		sql.append(", SUM(CASE WHEN (sumRpt.Net_Pay - sumRpt.NET_INCOME) > 0 ");
		sql.append("	THEN(sumRpt.Net_Pay - sumRpt.NET_INCOME) ELSE 0 END) AS NET_PAY");

		sql.append(", SUM(CASE WHEN (sumRpt.NET_INCOME - sumRpt.Net_Pay) > 0 ");
		sql.append("	THEN(sumRpt.NET_INCOME - sumRpt.Net_Pay) ELSE 0 END) AS NET_INCOME");

		sql.append(", SUM(sumRpt.NET_INCOME - sumRpt.NET_PAY) AS NET_INCOME_PAY_DIFF  ");

		sql.append(", SUM(CASE WHEN (sumRpt.Net_Pay - sumRpt.NET_INCOME) > 0 ");
		sql.append("	THEN 1 ELSE 0 END) AS Net_Pay_COUNT ");

		sql.append(", SUM(CASE WHEN (sumRpt.NET_INCOME - sumRpt.Net_Pay) > 0 ");
		sql.append("	THEN 1 ELSE 0 END) AS NET_INCOME_COUNT ");

		sql.append(" from ( ");
		sql.append(" SELECT COUNT(*) AS RECORD_COUNT,sum(D.Net_Pay) net_pay ");
		sql.append(" ,sum(d.net_income) net_income ");
		
		sql.append(" FROM DELIVERY_PAYMENT_CHK D");
		
		sql.append(" WHERE UPLOAD_FILE_PK = UPLOAD_FILE_PK ");
		sql.append(" group by d.account_no");

		sql.append(" ) sumRpt ");

		parameters.put("UPLOAD_FILE_PK", pk);

		SqlQueryMapProvider sqlQueryMapProvider = new SqlQueryMapProvider(sql.toString(), parameters);

		logger.debug("{}", sqlQueryMapProvider);

		return sqlQueryMapProvider;
	}


}